const express = require('express');
const router = express.Router();
const { pool } = require('../config/database');
const { authMiddleware, adminMiddleware } = require('./auth');
const dayjs = require("dayjs")
// 获取所有图书
router.get('/', async (req, res) => {
  try {
    const { search, category, page = 1, limit = 10 } = req.query;
    const offset = (page - 1) * limit;

    let query = 'SELECT * FROM books';
    let params = [];

    if (search || category) {
      query += ' WHERE';
      if (search) {
        query += ' (title LIKE ? OR author LIKE ? OR isbn LIKE ?)';
        params.push(`%${search}%`, `%${search}%`, `%${search}%`);
      }
      if (category) {
        if (search) query += ' AND';
        query += ' category = ?';
        params.push(category);
      }
    }

    query += ' LIMIT ? OFFSET ?';
    params.push(Number(limit), Number(offset));

    const [books] = await pool.execute(query, params);
    const [total] = await pool.execute('SELECT COUNT(*) as count FROM books');

    res.json({
      code: 0,
      msg: '获取所有图书成功',
      data: {
        books,
        total: total[0].count,
        page: Number(page),
        totalPages: Math.ceil(total[0].count / limit)
      }
    });
  } catch (error) {
    console.error(error);
    res.status(500).json({ code: -1, msg: '服务器错误', data: {} });
  }
});

// 获取单本图书详情
router.get('/:id', async (req, res) => {
  try {
    // 获取图书基本信息
    const [books] = await pool.execute(
      'SELECT * FROM books WHERE id = ?',
      [req.params.id]
    );

    if (books.length === 0) {
      return res.status(404).json({ code: -1, msg: '图书不存在', data: {} });
    }

    const book = books[0];

    // 获取图书评论
    const [comments] = await pool.execute(
      `SELECT c.*, u.username 
       FROM book_comments c 
       LEFT JOIN users u ON c.user_id = u.id 
       WHERE c.book_id = ? 
       ORDER BY c.created_at DESC 
       LIMIT 10`,
      [req.params.id]
    );

    // 获取借阅状态
    const [borrowStatus] = await pool.execute(
      `SELECT COUNT(*) as borrow_count 
       FROM borrows 
       WHERE book_id = ? AND status = 'borrowed'`,
      [req.params.id]
    );

    // 组合返回数据
    res.json({
      code: 0, msg: '成功', data: {
        ...book,
        comments,
        current_borrows: borrowStatus[0].borrow_count,
        is_available: book.available_copies > 0
      }
    });

  } catch (error) {
    console.error(error);
    res.status(500).json({ code: -1, msg: '服务器错误', data: {} });
  }
});

// 添加图书评论
router.post('/:id/comments', authMiddleware, async (req, res) => {
  try {
    const { content, rating } = req.body;
    const newTime = dayjs(new Date()).format("YYYY-MM-DD HH:mm:ss")
    if (!content) {
      return res.status(400).json({ code: -1, msg: '评论内容不能为空', data: {} });
    }

    const [result] = await pool.execute(
      'INSERT INTO book_comments (book_id, user_id, content, rating, created_at) VALUES (?, ?, ?, ?, ?)',
      [req.params.id, req.user.id, content, rating || 5, newTime]
    );

    res.status(201).json({
      code: 0,
      msg: '评论添加成功',
      data: {
        id: result.insertId
      }
    });
  } catch (error) {
    console.error(error);
    res.status(500).json({ code: -1, msg: '服务器错误', data: {} });
  }
});

// 添加新图书（需要管理员权限）
router.post('/', [authMiddleware, adminMiddleware], async (req, res) => {
  const { title, author, isbn, category, description, total_copies, publishing_house } = req.body;
  if (!title && !author && !isbn && !category && !total_copies && !publishing_house) {
    return res.status(400).json({
      code: -1,
      msg: "缺少必要的参数"
    })
  }
  if (isbn.trim().length !== 13) {
    return res.status(400).json({
      code: -1,
      msg: "图书编号应为13位数"
    })
  }
  const new_isbn = isbn.trim()
  const newTiem = dayjs(new Date()).format("YYYY-MM-DD HH:mm:ss")
  try {
    // 检查ISBN是否已存在
    const [existing] = await pool.execute(
      'SELECT * FROM books WHERE isbn = ?',
      [new_isbn]
    );

    if (existing.length > 0) {
      return res.status(400).json({ code: -1, msg: 'ISBN已存在', data: {} });
    }

    const [result] = await pool.execute(
      'INSERT INTO books (title, author, isbn, category, description, total_copies, available_copies, publishing_house,created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [title, author, new_isbn, category, description, total_copies, total_copies, publishing_house, newTiem]
    );

    res.status(200).json({
      code: 0,
      msg: '图书添加成功',
      data: {
        id: result.insertId
      }
    });
  } catch (error) {
    console.error(error);
    res.status(500).json({ code: -1, msg: '服务器错误', data: {} });
  }
});

// 更新图书信息（需要管理员权限）
router.put('/:id', [authMiddleware, adminMiddleware], async (req, res) => {
  try {
    const { title, author, isbn, category, description, total_copies } = req.body;
    const newTime = dayjs(new Date()).format("YYYY-MM-DD HH:mm:ss")
    // 检查图书是否存在
    const [books] = await pool.execute(
      'SELECT * FROM books WHERE id = ?',
      [req.params.id]
    );

    if (books.length === 0) {
      return res.status(404).json({ code: -1, msg: '图书不存在', data: {} });
    }

    // 检查ISBN是否与其他图书重复
    const [existing] = await pool.execute(
      'SELECT * FROM books WHERE isbn = ? AND id != ?',
      [isbn, req.params.id]
    );

    if (existing.length > 0) {
      return res.status(400).json({ code: -1, msg: 'ISBN已被其他图书使用', data: {} });
    }

    // 计算可用副本数的变化
    const book = books[0];
    const copiesDiff = total_copies - book.total_copies;
    const newAvailableCopies = book.available_copies + copiesDiff;

    await pool.execute(
      'UPDATE books SET title = ?, author = ?, isbn = ?, category = ?, description = ?, total_copies = ?, available_copies = ?, updated_at = ? WHERE id = ?',
      [title, author, isbn, category, description, total_copies, newAvailableCopies, newTime, req.params.id]
    );

    res.json({ code: 0, msg: '图书更新成功', data: {} });
  } catch (error) {
    console.error(error);
    res.status(500).json({ code: -1, msg: '服务器错误', data: {} });
  }
});

// 删除图书（需要管理员权限）
router.delete('/:id', [authMiddleware, adminMiddleware], async (req, res) => {
  try {
    // 检查图书是否存在
    const [books] = await pool.execute(
      'SELECT * FROM books WHERE id = ?',
      [req.params.id]
    );

    if (books.length === 0) {
      return res.status(404).json({ code: -1, msg: '图书不存在', data: {} });
    }

    // 检查是否有未归还的借阅记录
    const [borrows] = await pool.execute(
      'SELECT * FROM borrows WHERE book_id = ? AND status = "borrowed"',
      [req.params.id]
    );

    if (borrows.length > 0) {
      return res.status(400).json({ msg: '该图书还有未归还的借阅记录，无法删除' });
    }

    await pool.execute('DELETE FROM books WHERE id = ?', [req.params.id]);

    res.json({ code: 0, msg: '图书删除成功' });
  } catch (error) {
    console.error(error);
    res.status(500).json({ code: -1, msg: '服务器错误', data: {} });
  }
});

// 获取图书的所有分类
router.get("/types/all", authMiddleware, async (req, res) => {
  try {
    const [allType] = await pool.execute(
      `
      SELECT COLUMN_TYPE 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_NAME = 'books' AND COLUMN_NAME = 'category'
      `
    )
    // 解析 ENUM 值
    const enumStr = allType[0].COLUMN_TYPE; // "enum('艺术','旅游',...)"
    const enumValues = enumStr
      .slice(6, -1)          // 移除 "enum(" 和 ")"
      .split(',')            // 拆分成数组
      .map(v => v.trim().replace(/'/g, '')); // 移除单引号和空格

    console.log(enumValues);
    res.json({
      code: 0,
      msg: "获取图书的所有分类成功",
      data: enumValues
    })
  } catch (error) {
    console.error(error);
    res.status(500).json({ code: -1, msg: '服务器错误', data: {} });
  }
})

module.exports = router;